function rslt = est_daily_stat_measures( horizon, save2db)
% EST_STAT_MEASURES: estimate statistical measures for a given stock
% - return (mean, median, std, skew, kurtosis)
% - intraday volatility
% - max drawdown
% - turnover (in % and in cash)
% CAPM-based stats (w.r.t. VNINDEX)
% - alpha 
% - beta 
% - tracking-error (std error of residus)
%
% By default: 
% horizon = 1 year (260 days), otherwise must be specified in days
% save2db = 0 (set to 1 to save to database)

persistent dbConn
if isempty(dbConn)
    dbConn = createdbConn;
end

if (nargin == 0)
    horizon = 260;
end
if (nargin <= 1)
    save2db = false;
end
f_return = @(x) diff(x)./x(1:end-1);

% get all sec_ids
all_sec_ids = get_info_from_database('member', 'all');

date_now = datestr(now, 'dd/mm/yyyy');
date_from = datestr(datewrkdy(floor(now), -horizon), 'dd/mm/yyyy');
date_s = datestr(now, 'yyyy-mm-dd');
index_data = get_info_from_database('security', 'sec_id', 1001, ...
    'from', date_from, 'to', date_now);

if save2db
    insert_template_sql = 'INSERT IGNORE INTO prj_quant_data.daily_stats VALUES %s';
    value_template_sql = ['(%d, %d, ''%s'', %4.4f, %4.4f, %4.4f, %4.4f, ' ...
        '%4.4f, %4.4f, %4.4f, %4.4f, %4.4f, %4.4f, %4.4f, %4.4f, ' ...
        '%4.4f, %4.4f, %4.4f, %4.4f)'];
else
    rslt = NaN(size(all_sec_ids,1),17);
end
for s=1:numel(all_sec_ids)
    
    sec_id = all_sec_ids(s);
    trading_destination_id = get_info_from_database('trading_destination_id', sec_id);
    fprintf('Compute statistics for %s ', get_info_from_database('ticker', sec_id));
    
    stock_data = get_info_from_database('security', 'sec_id', sec_id, ...
        'from', date_from, 'to', date_now);
    if ~isempty(stock_data)
        % estimate statistis for stock
        price = stock_data(:,6);
        volume = stock_data(:,2);        
        ret = f_return(price);
        
        mean_s = mean(ret);
        std_s = std(ret);
        median_s = median(ret);
        skew_s = skewness(ret);
        kurtosis_s = kurtosis(ret);
        
        gk_volatility_s = median(est_volat_gk (stock_data(:,3:6)));
        
        max_dd_s = maxdrawdown(price);
        
        turnover_pct = volume / get_info_from_database('floating', sec_id);        
        min_turnover_pct = min(turnover_pct);
        med_turnover_pct = median(turnover_pct);
        max_turnover_pct = max(turnover_pct);
        
        turnover_cash = price.*volume;
        min_turnover_in_cash = min(turnover_cash);
        med_turnover_in_cash = median(turnover_cash);
        max_turnover_in_cash = max(turnover_cash);
        
        
        % statistics related to index
        [c, i_stock, i_index] = intersect(stock_data(:,1), index_data(:,1));
        ret_stock_price = f_return(stock_data(i_stock, 6));
        ret_index = f_return(index_data(i_index, 6));
        
        [b, b_int, r] = regress(ret_stock_price, [ones(size(ret_index)), ret_index]);
        alpha = b(1);
        beta = b(2);
        tracking_error = std(r);
        
        if save2db
            vals = sprintf(value_template_sql, sec_id, trading_destination_id, date_s,...
                mean_s, std_s, median_s, skew_s, kurtosis_s,...
                gk_volatility_s, max_dd_s, min_turnover_pct, med_turnover_pct, max_turnover_pct,...
                min_turnover_in_cash, med_turnover_in_cash, max_turnover_in_cash,...
                alpha, beta, tracking_error);
            real_sql = sprintf(insert_template_sql, vals);
            rslt = exec(dbConn, real_sql);
        else
            rslt(s,:) = [sec_id, mean_s, std_s, median_s, skew_s, kurtosis_s,...
                gk_volatility_s, max_dd_s, min_turnover_pct, med_turnover_pct, max_turnover_pct,...
                min_turnover_in_cash, med_turnover_in_cash, max_turnover_in_cash,...
                alpha, beta, tracking_error];
        end
        fprintf('done.\n');
    else
        fprintf('nodata.\n');
    end
end

